package com.dshore.messagecenter.dao.system;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.dshore.messagecenter.model.system.SysDefined;
import com.dshore.messagecenter.model.system.SysDict;
import com.dshore.messagecenter.util.UUIDUtil;

@Repository
public class SysCustomProcessDao {
	@Autowired
	JdbcTemplate jdbcTemplate;
	//查询总数
		public Long queryCountMcAPP(SysDefined sysDefined) {
			StringBuffer sql = new StringBuffer();
			List<String> params=new ArrayList<String>();
			sql.append("select count(*) from T_SYS_DEFINED_HANDLE where del_flag=0");
			if(StringUtils.isNotBlank(sysDefined.getDefinedName())) {
				sql.append(" and DEFINED_NAME like ?");
				params.add("%"+sysDefined.getDefinedName()+"%");
			}
			if(StringUtils.isNotBlank(sysDefined.getDefinedVersion())) {
				sql.append(" and DEFINED_VERSION like ? ");
				params.add("%"+sysDefined.getDefinedVersion()+"%");
			}
			if(StringUtils.isNotBlank(sysDefined.getDefinedType())) {
				sql.append(" and DEFINED_TYPE= ? ");
				params.add(sysDefined.getDefinedType());
			}
			Object[] param=params.toArray();
			if(params.size()==0) {
				return jdbcTemplate.queryForObject(sql.toString(), Long.class);
			}else {
				return jdbcTemplate.queryForObject(sql.toString(), param, Long.class);
			}
		}
		//分页
		public List<SysDefined> queryListMcAPP(SysDefined sysDdfined) {
			StringBuffer sql = new StringBuffer();
			List<Object> params=new ArrayList<Object>();
			sql.append("select c.id,c.DEFINED_NAME,c.DEFINED_TYPE,c.DEFINED_CONTENT_NAME,c.CREATED_TIME,c.DEFINED_VERSION,c.DEFINED_CODE,"
					+ "t.DICT_name DefinedTypeName "+
			"from T_SYS_DEFINED_HANDLE c left join T_SYS_DICT t ON c.DEFINED_TYPE = t.DICT_code" + 
			" where  c.del_flag=0 and t.dict_type='defined_type'");
			if(StringUtils.isNotBlank(sysDdfined.getDefinedName())) {
				sql.append(" and c.DEFINED_NAME like ?");
				params.add("%"+sysDdfined.getDefinedName()+"%");
			}
			if(StringUtils.isNotBlank(sysDdfined.getDefinedVersion())) {
				sql.append(" and c.DEFINED_VERSION like ? ");
				params.add("%"+sysDdfined.getDefinedVersion()+"%");
			}
			if(StringUtils.isNotBlank(sysDdfined.getDefinedType())) {
				sql.append(" and c.DEFINED_TYPE= ? ");
				params.add(sysDdfined.getDefinedType());
			}
			sql.append(" ORDER BY c.CREATED_TIME DESC" ); 
			 String sql1=" select * from ( select A.*, ROWNUM AS RN  from( " + sql + " ) A  "
			 		+ "where  ROWNUM<= ? )where rn > ? order by CREATED_TIME desc";
			 Integer Limit=sysDdfined.getLimit()+sysDdfined.getOffset();
			 Integer Offset=sysDdfined.getOffset();
			 params.add(Limit);
			 params.add(Offset);
			 Object[] param=params.toArray();
			 RowMapper<SysDefined> mcAPPRowMapper = new BeanPropertyRowMapper<>(SysDefined.class);
			 return jdbcTemplate.query(sql1, param, mcAPPRowMapper);
		}
	//查询自定义类型
	public List<SysDict> selectByStatus() {
		String sql = "select DICT_name , DICT_code  from T_SYS_DICT where dict_type ='defined_type'"
				+ " ORDER BY SEQ";
		RowMapper<SysDict> mcAPPRowMapper = new BeanPropertyRowMapper<>(SysDict.class);
		return jdbcTemplate.query(sql, mcAPPRowMapper);
	}
	//批量删除（逻辑删除）
	public int[] deleteAll(String[] ids) {
	   StringBuffer sql = new StringBuffer();
	   final List<Object> updateParams=new ArrayList<>();
	   for(int i=0;i<ids.length;i++) {
		   String id=ids[i];
		   updateParams.add(id);
		}
		sql.append("update T_SYS_DEFINED_HANDLE set del_flag = 1,modified_time=? where id = ? ");
		return jdbcTemplate.batchUpdate(sql.toString(),new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
				ps.setString(2, updateParams.get(i).toString());
			}
			@Override
			public int getBatchSize() {
				return updateParams.size();
			}
		});
	}
	//编码
	public List<SysDefined> checkCode() {
		String sql = "select * from (SELECT DEFINED_CODE from T_SYS_DEFINED_HANDLE ORDER"
				+ " BY CREATED_TIME desc)where ROWNUM=1";
		RowMapper<SysDefined> mcAPPRowMapper = new BeanPropertyRowMapper<>(SysDefined.class);
		return jdbcTemplate.query(sql, mcAPPRowMapper);
	}
	//新增
	public Integer insertSysDefined(SysDefined sysdefined) {
		String sql= "insert into T_SYS_DEFINED_HANDLE (id, DEFINED_NAME, "+
		 "DEFINED_TYPE,DEFINED_VERSION,CREATED_TIME,MODIFIED_TIME, "+
		  "DEL_FLAG,DEFINED_CODE,DEFINED_CONTENT_NAME,DEFINED_CONTENT)" +
		  	" values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		return jdbcTemplate.update(sql, UUIDUtil.getUUId(),sysdefined.getDefinedName(),
				sysdefined.getDefinedType(),sysdefined.getDefinedVersion(),
				new Date(),new Date(),
				0,sysdefined.getDefinedCode(),
				sysdefined.getDefinedContentName(),sysdefined.getDefinedContent());
	}
	//查询数据
	public SysDefined selectBySysDefined(String id) {
		String sql = "select id,DEFINED_NAME,DEFINED_TYPE,DEFINED_VERSION,"+
				"DEFINED_CODE,DEFINED_CONTENT_NAME from T_SYS_DEFINED_HANDLE where ID=?";
		RowMapper<SysDefined> mcAPPRowMapper = new BeanPropertyRowMapper<>(SysDefined.class);
		return jdbcTemplate.queryForObject(sql, mcAPPRowMapper,id);
	}
	//删除数据（物理删除）
	public Integer deleteBySysDefined(String id) {
		String sql = "delete from T_SYS_DEFINED_HANDLE where ID=?";
	    return jdbcTemplate.update(sql,id);
	}
	//新增
		public Integer insertSysDefinedStorager(SysDefined sysdefined) {
			String sql= "insert into T_SYS_DEFINED_HANDLE_HIS (id, DEFINED_NAME, "+
			 "DEFINED_TYPE,DEFINED_VERSION,CREATED_TIME,MODIFIED_TIME, "+
			  "DEL_FLAG,DEFINED_CODE,DEFINED_CONTENT_NAME,DEFINED_CONTENT)" +
			  	" values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			return jdbcTemplate.update(sql, UUIDUtil.getUUId(),sysdefined.getDefinedName(),
					sysdefined.getDefinedType(),sysdefined.getDefinedVersion(),
					new Date(),new Date(),
					0,sysdefined.getDefinedCode(),
					sysdefined.getDefinedContentName(),sysdefined.getDefinedContent());
		}
		//校验姓名
		public List<SysDefined> checkName(String DefinedName) {
			String sql = "select DEFINED_NAME , id  from T_SYS_DEFINED_HANDLE where DEFINED_NAME =?";
			RowMapper<SysDefined> mcAPPRowMapper = new BeanPropertyRowMapper<>(SysDefined.class);
			return jdbcTemplate.query(sql, mcAPPRowMapper,DefinedName);
		}
		
		//校验版本号
		public String checkVersion(String id) {
			String sql="select DEFINED_VERSION from T_SYS_DEFINED_HANDLE where id=?";
			return jdbcTemplate.queryForObject(sql, String.class,id);
		}
		//查看历史记录
		public List<SysDefined> getByDefinedCode(String DefinedCode) {
			String sql ="select c.id,c.DEFINED_NAME,c.DEFINED_TYPE,c.DEFINED_VERSION," + 
			"c.DEFINED_CODE,c.DEFINED_CONTENT_NAME,c.CREATED_TIME,"+
			" t.DICT_name DefinedTypeName from "+
			" T_SYS_DEFINED_HANDLE_HIS c left join T_SYS_DICT t ON c.DEFINED_TYPE = t.DICT_code "
			+ " where c.DEFINED_CODE =? order by c.CREATED_TIME desc";
			RowMapper<SysDefined> mcAPPRowMapper = new BeanPropertyRowMapper<>(SysDefined.class);
			return jdbcTemplate.query(sql, mcAPPRowMapper,DefinedCode);
		}
		//历史记录的下载
		public SysDefined selectById(String id) {
			String sql = "select DEFINED_CONTENT_NAME , DEFINED_CONTENT  from T_SYS_DEFINED_HANDLE_HIS where id =?";
			RowMapper<SysDefined> mcAPPRowMapper = new BeanPropertyRowMapper<>(SysDefined.class);
			return jdbcTemplate.queryForObject(sql, mcAPPRowMapper,id);
		}
		//表格中的下载
		public SysDefined Newdownload(String id) {
			String sql = "select DEFINED_CONTENT_NAME , DEFINED_CONTENT  from T_SYS_DEFINED_HANDLE where id =?";
			RowMapper<SysDefined> mcAPPRowMapper = new BeanPropertyRowMapper<>(SysDefined.class);
			return jdbcTemplate.queryForObject(sql, mcAPPRowMapper,id);
		}
		
		//模态框查询总数
				public Long queryCountCustomCode(SysDefined sysDefined) {
					String sql = "select count(*) from T_SYS_DEFINED_HANDLE_HIS where del_flag=0 and DEFINED_CODE=?";
					return jdbcTemplate.queryForObject(sql,Long.class, sysDefined.getDefinedCode());
				}
				public List<SysDefined> queryListCustomCode(SysDefined sysDdfined) {
					StringBuffer sql = new StringBuffer();
					List<Object> params=new ArrayList<Object>();
					sql.append("select c.id,c.DEFINED_NAME,c.DEFINED_TYPE,c.DEFINED_CONTENT_NAME,c.CREATED_TIME,c.DEFINED_VERSION,c.DEFINED_CODE,"
							+ "t.DICT_name DefinedTypeName "+
					"from T_SYS_DEFINED_HANDLE_HIS c left join T_SYS_DICT t ON c.DEFINED_TYPE = t.DICT_code" + 
					" where  c.del_flag=0 and t.dict_type='defined_type' and c.DEFINED_CODE=?");
					params.add(sysDdfined.getDefinedCode());
					 sql.append(" ORDER BY c.CREATED_TIME DESC" ); 
					 String sql1=" select * from ( select A.*, ROWNUM AS RN  from( " + sql + " ) A  "
					 		+ "where  ROWNUM<= ? )where rn > ? order by CREATED_TIME desc";
					 System.out.println(sql1);
					 Integer Limit=sysDdfined.getLimit()+sysDdfined.getOffset();
					 Integer Offset=sysDdfined.getOffset();
					 params.add(Limit);
					 params.add(Offset);
					 Object[] param=params.toArray();
					 RowMapper<SysDefined> mcAPPRowMapper = new BeanPropertyRowMapper<>(SysDefined.class);
					 return jdbcTemplate.query(sql1, param, mcAPPRowMapper);
				}
		
		public String selectPublicKey() {
			String sql = "select DICT_NAME from T_SYS_DICT where dict_type ='public_key' and dict_code = '3201'";
			try {
				return jdbcTemplate.queryForObject(sql, String.class);
			} catch (DataAccessException d) {
				d.printStackTrace();
				return null;
			}
		}
}
